package com.mycompany.inventory.persistence;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.persistence.CustomFieldDAO;
import com.mycompany.inventory.pojo.CustomInfo;
import com.mycompany.inventory.pojo.OrderDetails;
import com.mycompany.inventory.pojo.Product;
import com.mycompany.inventory.pojo.ProductInventory;
import com.mycompany.inventory.pojo.Purchase;
import com.mycompany.inventory.pojo.SaleInfo;


/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class PurchaseDAO extends AbstractDao<Purchase>{
	
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_purchase_order order by createdStamp DESC";
	public static final String DELETE_SQL = "DELETE FROM oce_purchase_order WHERE purchaseOrderId = ?";
	public static final String INSERT_SQL = "insert into oce_purchase_order (purchaseOrderId, orderId, vendor_id, purchase_date, isStatus, paid, remarks," +
			   "date_paid, return_date, currencyId, taxSchemeId, paymentTermId, paymentMethodId, locationId, carrierId, rep_ship_date, freight) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	public static final String UPDATE_SQL = "UPDATE oce_purchase_order SET vendor_id = ?, purchase_date = ?, taxSchemeId = ? , " +
		                                	"paymentTermId = ? , currencyId = ?, rep_ship_date = ?, freight = ? , remarks = ? " +
		                                	"WHERE purchaseOrderId = ? ";
	public static final String UPDATE_REMARK_RECEIVE = "update oce_purchase_order SET remark_receive = ? where purchaseOrderId = ?";
	public static final String UPDATE_PAYMENT = "update oce_purchase_order SET paymentMethodId = ?, due_date = ?, date_paid = ?, remark_payment = ?, "+
	"paid = ? where purchaseOrderId = ?";
	public static final String UPDATE_RETURN = "update oce_purchase_order SET return_date = ?, remark_return = ?, refund = ?, "+
	"fee = ? where purchaseOrderId = ?";
	public static final String UPDATE_REMARK_UNSTOCK = "update oce_purchase_order SET remark_unstock = ? where purchaseOrderId = ?";
	public static final String UPDATE_STATUS = "update oce_purchase_order SET isStatus = ? where purchaseOrderId = ?";

	public Purchase create(Purchase obj){
		try{
			
			con.setAutoCommit(false);
			
			if(obj == null || obj.getId() == null || obj.getId().equals(""))
				return obj;				
			
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getOrderId());
			
			if(obj.getVendor()== null || obj.getVendor().getId().equals("")){
				ps.setLong(3, 0);
			}else{
				ps.setLong(3,Long.parseLong(obj.getVendor().getId()));
			}
			
			if(obj.getPurchaseDate()== null || obj.getPurchaseDate().equals("")){
				ps.setDate(4, null);
			}else{
				ps.setDate(4, obj.getPurchaseDate());
			}			
			
			ps.setString(5, obj.getStatus());
			ps.setDouble(6, obj.getPaid());
			ps.setString(7, obj.getRemark());
			
			if(obj.getDatePaid()== null || obj.getDatePaid().equals("")){
				ps.setDate(8, null);
			}else{
				ps.setDate(8, obj.getDatePaid());
			}
			
			if(obj.getReturnDate()== null || obj.getReturnDate().equals("")){
				ps.setDate(9, null);
			}else{
				ps.setDate(9, obj.getReturnDate());
			}			
			
			if(obj.getCurrency()== null || obj.getCurrency().getId().equals("")){
				ps.setLong(10, 0);
			}else{
				ps.setLong(10,Long.parseLong(obj.getCurrency().getId()));
			}
			
			if(obj.getTaxScheme()== null || obj.getTaxScheme().getId().equals("")){
				ps.setLong(11, 0);
			}else{
				ps.setLong(11,Long.parseLong(obj.getTaxScheme().getId()));
			}
			
			if(obj.getPaymentTerm()== null || obj.getPaymentTerm().getId().equals("")){
				ps.setLong(12, 0);
			}else{
				ps.setLong(12,Long.parseLong(obj.getPaymentTerm().getId()));
			}
			
			if(obj.getPaymentMethod()== null || obj.getPaymentMethod().getId().equals("")){
				ps.setLong(13, 0);
			}else{
				ps.setLong(13,Long.parseLong(obj.getPaymentMethod().getId()));
			}
			
			if(obj.getInventory()== null || obj.getInventory().getId().equals("")){
				ps.setLong(14, 0);
			}else{
				ps.setLong(14,Long.parseLong(obj.getInventory().getId()));
			}
			
			if(obj.getCarrier()== null || obj.getCarrier().getId().equals("")){
				ps.setLong(15, 0);
			}else{
				ps.setLong(15,Long.parseLong(obj.getCarrier().getId()));
			}			
			ps.setDate(16, obj.getReqShipDate());
			ps.setString(17, obj.getFreight());			
			
			ConnectionDatabase.executeUpdate(ps);
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
            
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Purchase update(Purchase obj){
		try{
			
			con.setAutoCommit(false);
			
			if(obj == null || obj.getId() == null || obj.getId().equals(""))
				return obj;				
			
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			
			if(obj.getVendor()== null || obj.getVendor().getId().equals("")){
				ps.setLong(1, 0);
			}else{
				ps.setLong(1,Long.parseLong(obj.getVendor().getId()));
			}
			
			if(obj.getPurchaseDate()== null || obj.getPurchaseDate().equals("")){
				ps.setDate(2, null);
			}else{
				ps.setDate(2, obj.getPurchaseDate());
			}			
			
			if(obj.getTaxScheme()== null || obj.getTaxScheme().getId().equals("")){
				ps.setLong(3, 0);
			}else{
				ps.setLong(3,Long.parseLong(obj.getTaxScheme().getId()));
			}
			
			if(obj.getPaymentTerm()== null || obj.getPaymentTerm().getId().equals("")){
				ps.setLong(4, 0);
			}else{
				ps.setLong(4,Long.parseLong(obj.getPaymentTerm().getId()));
			}

			if(obj.getCurrency()== null || obj.getCurrency().getId().equals("")){
				ps.setLong(5, 0);
			}else{
				ps.setLong(5,Long.parseLong(obj.getCurrency().getId()));
			}			
			
			ps.setDate(6, obj.getReqShipDate());
			ps.setString(7, obj.getFreight());			
			ps.setString(8, obj.getRemark());
			ps.setLong(9, Long.parseLong(obj.getId()));
			ConnectionDatabase.executeUpdate(ps);
           
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public void updatePaymentPurchase(Purchase obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_PAYMENT, PreparedStatement.RETURN_GENERATED_KEYS);	
			
			if(obj.getPaymentMethod()== null || obj.getPaymentMethod().getId().equals("")){
				ps.setLong(1, 0);
			}else{
				ps.setLong(1,Long.parseLong(obj.getPaymentMethod().getId()));
			}
			ps.setDate(2, obj.getDatePaid());
			ps.setDate(3, obj.getDueDate());
			ps.setString(4, obj.getRemarkPayment());
			ps.setDouble(5, obj.getPaid());
			ps.setLong(6, Long.parseLong(obj.getId()));			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}		
	}
	
	public void updateReturnPurchase(Purchase obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_RETURN, PreparedStatement.RETURN_GENERATED_KEYS);	
			
			ps.setDate(1, obj.getReturnDate());
			ps.setString(2, obj.getRemarkReturn());
			ps.setDouble(3, obj.getRefund());
			ps.setDouble(4, obj.getFee());
			ps.setLong(5, Long.parseLong(obj.getId()));			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}		
	}
	
	public void updateUnstockPurchase(Purchase obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_REMARK_UNSTOCK, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setString(1, obj.getRemarkUnstock());
			ps.setLong(2, Long.parseLong(obj.getId()));			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}		
	}
	
	public void updateStatus(Purchase obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_STATUS, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setString(1, obj.getStatus());
			ps.setLong(2, Long.parseLong(obj.getId()));			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}		
	}		
	
	public Purchase find(long id){
		Purchase purchase = new Purchase();
		try{
			
		}catch (Exception e) {
			// TODO: handle exception
		}
		return purchase;
	}
	
	public List<Purchase> findAll(){
		List<Purchase> purchase = new ArrayList<Purchase>();
		try{
			purchase = this.convert(ConnectionDatabase.executeQuery(this.con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			purchase = null;
		}
		return purchase;
	}
	
	public void delete(Purchase obj){
		try{
			con.setAutoCommit(false);			
		
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getId()));
			
			ConnectionDatabase.executeUpdate(ps);	
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}		
	}
	
	public Purchase updateRemarkReceive(Purchase obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_REMARK_RECEIVE, PreparedStatement.RETURN_GENERATED_KEYS);	
			ps.setString(1, obj.getRemarkReceive());
			ps.setLong(2, Long.parseLong(obj.getId()));
			
			ConnectionDatabase.executeUpdate(ps);
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}	
	
	private List<Purchase> convert(ResultSet rs) throws SQLException {
		List<Purchase> purchase = new ArrayList<Purchase>();
		InventoryDAO inDao = new InventoryDAO();
		VendorDAO vDao = new VendorDAO();
		CurrencyDAO cDao = new CurrencyDAO();
		TaxSchemeDAO tDao = new TaxSchemeDAO();
		PaymentTermDAO pDao = new PaymentTermDAO();
		PaymentMethodDAO mDao = new PaymentMethodDAO();
		CarrierDAO rDao = new CarrierDAO();
		
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String orderId = rs.getString(2);
	        String vendorId = rs.getString(3);
	        Date purchase_date = rs.getDate(4);
	        String status = rs.getString(5);
	        int paid = rs.getInt(6);
	        String remark = rs.getString(7);
	        Date date_paid = rs.getDate(8);
	        Date return_date = rs.getDate(9);
	        String currencyId = rs.getString(10);
	        String taxSchemeId = rs.getString(11);
	        String paymentTermId = rs.getString(12);
	        String paymentMethodId = rs.getString(13);
	        String locationId = rs.getString(14);        
	        String carrierId = rs.getString(15);
	        Date ship = rs.getDate(16);
	        String freight = rs.getString(17);
	        String remarkReceive = rs.getString(18);
	        String remarkPayment = rs.getString(19);
	        String remarkReturn = rs.getString(20);
	        double refun = rs.getDouble(21);
	        String remarkUnstock = rs.getString(22);
	        double fee = rs.getDouble(23);
	        Date dueDate = rs.getDate(24);
        
	        purchase.add(new Purchase(id, orderId,vendorId == null ? null : vDao.find(Long.parseLong(vendorId)), purchase_date, status, paid, remark, date_paid, return_date, currencyId == null ? null : cDao.find(Long.parseLong(currencyId)), 
	        		taxSchemeId == null ? null : tDao.find(Long.parseLong(taxSchemeId)), paymentTermId == null ? null : pDao.find(Long.parseLong(paymentTermId)), paymentMethodId == null ? null : mDao.find(Long.parseLong(paymentMethodId)), locationId == null ? null : inDao.find(Long.parseLong(locationId)), carrierId == null ? null : rDao.find(Long.parseLong(carrierId)), ship, freight,
	        				remarkReceive, remarkPayment, remarkReturn, refun, remarkUnstock, fee, dueDate));
	    }
		rs.close();
		return purchase;
	}	
}
